package com.aboo.infrastructure.jdbc;

/*
 * @description
 * @author Yata.Niko
 * @version 1.0
 * @date 2024/3/30
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLCRUDExample {

    private static final String DB_URL = "jdbc:mysql://192.168.8.8:3306/demo_db?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "root";

    public static void main(String[] args) {
        Connection conn = null;
        try {
            // 1. Establish a connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // 2. Perform CRUD operations

            // CREATE (Insert)
            createUser(conn, "012", "鳄鱼", 8);

            // READ (Select)
            printAllUsers(conn);

            // UPDATE (Update)
            updateUser(conn, 11, "013", "鬣狗", 3);

            // DELETE (Delete)
            deleteUser(conn, 12);

            // 3. Close the connection
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createUser(Connection conn, String userCode, String userName, Integer age) throws SQLException {
        String sql = "INSERT INTO usr_user (user_code, user_name, age) VALUES (?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userCode);
        pstmt.setString(2, userName);
        pstmt.setInt(3, age);
        pstmt.executeUpdate();
        System.out.println("User created successfully.");
    }

    private static void printAllUsers(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM usr_user");
        while (rs.next()) {
            System.out.printf("ID: %d, user_code: %s, user_name: %s, age: %s%n",
                    rs.getInt("id"), rs.getString("user_code"), rs.getString("user_name"), rs.getInt("age"));
        }
        rs.close();
    }

    private static void updateUser(Connection conn, int id, String userCode, String userName, Integer age) throws SQLException {
        String sql = "UPDATE usr_user SET user_code = ?, user_name = ?, age = ? WHERE id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userCode);
        pstmt.setString(2, userName);
        pstmt.setInt(3, age);
        pstmt.setInt(4, id);
        pstmt.executeUpdate();
        System.out.println("User updated successfully.");
    }

    private static void deleteUser(Connection conn, int id) throws SQLException {
        String sql = "DELETE FROM usr_user WHERE id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        pstmt.executeUpdate();
        System.out.println("User deleted successfully.");
    }
}
